#include "adDao.h"
#include "sqlite3.h"

//插入数据
int insertAd(Ad *ad)
{
    sqlite3 *db = NULL;
    int res = sqlite3_open(AD_DB_PATH, &db);
    if (res != 0)
    {
        perror("open db error");
        return ;
    }

    char *sql = (char *)calloc(1, 256);
    sprintf(sql, "insert into t_ad(id,`index`,start_time,end_time) values(%d,%d,'%s','%s');",ad->id,ad->index,ad->start_time,ad->end_time);

    char *errmsg;
    sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (errmsg != NULL)
    {
        printf("%s\n", errmsg);
    }

    //释放内存
    sqlite3_close(db);

    return 0;
}

//修改数据
int updateAd(Ad *ad)
{
    //声明连接
    sqlite3 *db = NULL;
    //打开sql
    int res = sqlite3_open(AD_DB_PATH, &db);
    if (res != 0)
    {
        perror("open db error");
        return ;
    }

    //构建sql
    char sql[256];
    sprintf(sql, "update t_ad set `index`=%d,start_time='%s',end_time='%s';",ad->index,ad->start_time,ad->end_time);

    //执行sql
    char *errmsg;
    sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (errmsg != NULL)
    {
        printf("%s\n", errmsg);
    }

    //释放内存
    sqlite3_close(db);

    return 0;
}

//根据id查询数据
Ad *getByIdAd(int id)
{
    //声明连接
    sqlite3 *db = NULL;
    //打开sql
    int res = sqlite3_open(AD_DB_PATH, &db);
    if (res != 0)
    {
        perror("open db error");
        return ;
    }

    char sql[256];
    char *errmsg;
    //结果集
    char **result;
    int row;
    int col;
    sprintf(sql,"select * from t_ad where id = %d;",id);
    res = sqlite3_get_table(db, sql, &result, &row, &col, &errmsg);
    if (res != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }

    //打印数据
    int  index = col;
    printf( "查到%d条记录\n", row );
    for(  int i = 0; i < row ; i++ )
    {
        printf("第 %d 条记录\n", i+1 );
        Ad *ad = calloc(1, sizeof(Ad));
        for( int j = 0 ; j < col; j++ )
        {
            char *col_name =  result[j];
            char *col_val =  result[index];

            if (g_str_equal("id",col_name))
            {
                ad->id = atoi(col_val);
            }

            if (g_str_equal("index",col_name))
            {
                ad->index = atoi(col_val);
            }

            if (g_str_equal("start_time",col_name))
            {
                ad->start_time = col_val;
            }

            if (g_str_equal("end_time",col_name))
            {
                ad->end_time = col_val;
            }
            ++index;
        }
        return ad;
    }
    return NULL;
}



//插入数据
int insertAr(AdResource *ar)
{
    sqlite3 *db = NULL;
    int res = sqlite3_open(AD_DB_PATH, &db);
    if (res != 0)
    {
        perror("open db error");
        return ;
    }

    char *sql = (char *)calloc(1, 256);
    sprintf(sql, "insert into t_ad_resource(url,`index`,ad_id,sign,type_id) values('%s',%d,%d,'%s',%d);",ar->path,ar->index,ar->ad_id,ar->sign,ar->type_id);

    char *errmsg;
    sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (errmsg != NULL)
    {
        printf("%s\n", errmsg);
    }

    //释放内存
    sqlite3_close(db);

    return 0;   
}

//修改数据
int updateAr(AdResource *ar)
{
    //声明连接
    sqlite3 *db = NULL;
    //打开sql
    int res = sqlite3_open(AD_DB_PATH, &db);
    if (res != 0)
    {
        perror("open db error");
        return ;
    }

    //构建sql
    char sql[256];
    sprintf(sql, "update t_ad_resource set `index`=%d,url='%s',sign='%s',ad_id=%d,type_id=%d where id = %d;",ar->index,ar->path,ar->sign,ar->ad_id,ar->type_id,ar->id);

    //执行sql
    char *errmsg;
    sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (errmsg != NULL)
    {
        printf("%s\n", errmsg);
    }

    //释放内存
    sqlite3_close(db);

    return 0;
}

//根据id查询数据
AdResource *getByIdAr(int adid, int indexTemp)
{
    //声明连接
    sqlite3 *db = NULL;
    //打开sql
    int res = sqlite3_open(AD_DB_PATH, &db);
    if (res != 0)
    {
        perror("open db error");
        return ;
    }

    char sql[256];
    char *errmsg;
    //结果集
    char **result;
    int row;
    int col;
    sprintf(sql,"select * from t_ad_resource where ad_id = %d and `index`= %d;",adid,indexTemp);
    res = sqlite3_get_table(db, sql, &result, &row, &col, &errmsg);
    if (res != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }

    //打印数据
    int  index = col;
    printf( "查到%d条记录\n", row );
    for(  int i = 0; i < row ; i++ )
    {
        printf("第 %d 条记录\n", i+1 );
        AdResource *ar = calloc(1, sizeof(AdResource));
        for( int j = 0 ; j < col; j++ )
        {
            char *col_name =  result[j];
            char *col_val =  result[index];

            
            if (g_str_equal("id",col_name))
            {
                ar->id = atoi(col_val);
            }

            if (g_str_equal("index",col_name))
            {
                ar->index = atoi(col_val);
            }
                
            if (g_str_equal("ad_id",col_name))
            {
                ar->ad_id = atoi(col_val);
            }

            if (g_str_equal("type_id",col_name))
            {
                ar->type_id = atoi(col_val);
            }
                
            if (g_str_equal("sign",col_name))
            {
                g_print("%s==========================\n",col_val);
                ar->sign = col_val;
            }

            if (g_str_equal("url",col_name))
            {
            ar->path = col_val;
            }
            ++index;
        }
        return ar;
    }
    return NULL;
}



//清理
int clearExpireAd()
{
    sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db error!");
        return -1;
    }

    //执行
    char sql[256];

    sprintf(sql,"delete from t_ad where end_time < datetime(CURRENT_TIMESTAMP,'localtime');");
    char *errmsg;   
    //删除广告
    sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    //删除已经播放完毕的广告资源
    if (errmsg!=NULL)
    { 
        printf("%s\n",errmsg);
        return -1;
    }

    sprintf(sql,"delete from t_ad_resource where ad_id not in (select id from t_ad);");   
    //删除资源
    sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if (errmsg!=NULL)
    { 
        printf("%s\n",errmsg);
        return -1;
    }

    //删除本地路径文件->在删除数据的时候 同时清除对应的文件
    

    //释放
    sqlite3_close(db);

    return 0;
}